-- @author lixiyong01
-- @date 2024.05.10
--通过udf函数解析复杂度
--如果复杂度和表都一直，doc_id也是一样，就认为是一个任务， 注：有些查询的doc_id为负值，是没有存储的

use hdp_lbg_supin_zplisting;

add jar viewfs://58-cluster/home/hdp_lbg_supin/resultdata/offline_warehouse3/udf/warehouse_tool-1.0.23-shaded.jar;
create temporary function SqlComplexityUDF AS 'com.bj58.adsp.dp.hive.offline_udf.SqlComplexityUDF';

insert overwrite table dwd_zp_rock_health_query_complex partition (dt=${dateSuffix})
select a.user_oa, a.type, a.is_cron, a.hadoop_account,
    split(a.complex_content,'\\$')[1] as complex,
    split(a.complex_content,'\\$')[2] as complex_desc,
    split(a.complex_content,'\\$')[3] as complex_table,
    a.query_sql,
    b.user_name_cn, b.position_name, b.org_cn_name
from (
    select user_oa, type, is_cron_job as is_cron, hadoop_account, doc_id, query_sql, SqlComplexityUDF(query_sql) as complex_content
    from hdp_teu_dpd_feature_db.app_xh_task_execute_log_inc_d_hrg_view
    where dt='${#date(0, 0, -1):yyyyMMdd#}'
) a
left join (
    select username,
        realname AS user_name_cn, position_name,
        concat(
            split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-4],
            '/',split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-3],
            '/',split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-2],
            '/',split(org_cn_path,'\\\\')[size(split(org_cn_path,'\\\\'))-1]
        ) as org_cn_name
    from hdp_lbg_supin_zplisting.dim_zp_n_n_emp_org
    where dt='${#date(0, 0, -1):yyyyMMdd#}'
) b on a.user_oa=b.username